﻿using Aspose.Cells;
using NPOI.SS.Formula.PTG;
using System;
using System.Collections.Generic;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class LogDAL
    {
        public bool AddErrorLog(ErrorLogInfo errorLog)
        {
            string strSql = string.Format(@"INSERT INTO errorlog(operation,errorMsg,errorTime)VALUES('{0}','{1}','{2}')",
                errorLog.operation, errorLog.errorMsg, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            return SqlHelper<ErrorLogInfo>.SqlExecute(strSql);
        }

        public List<ErrorLogInfo> QueryErrorLog(ErrorLogInfo errorLog)
        {
            string strSql = "SELECT * FROM errorlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(errorLog.starttime))
            {
                sqlwhere += "DATE(errorTime)>=DATE('" + errorLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(errorLog.endtime))
            {
                sqlwhere += "DATE(errorTime)<=DATE('" + errorLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (errorLog.Recordhead > -1 && errorLog.Recordtail > 0)
            {
                strSql += " LIMIT " + errorLog.Recordhead + "," + errorLog.Recordtail;
            }
            return SqlHelper<ErrorLogInfo>.SqlList(strSql);
        }

        public int QueryErrorLogCount(ErrorLogInfo errorLog)
        {
            string strSql = "SELECT COUNT(1) FROM errorlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(errorLog.starttime))
            {
                sqlwhere += "DATE(errorTime)>=DATE('" + errorLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(errorLog.endtime))
            {
                sqlwhere += "DATE(errorTime)<=DATE('" + errorLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<ErrorLogInfo>.SqlScalar(strSql);
        }

        public bool AddOperationLog(OperationLog operationLog)
        {
            string strSql = string.Format(@"INSERT INTO operationlog(operation,operatorName,operationTime)VALUES('{0}','{1}','{2}')",
                operationLog.operation, operationLog.operatorName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<OperationLog>.SqlExecute(strSql);
        }

        public List<OperationLog> QueryOperationlog(OperationLog operationLog)
        {
            string strSql = "SELECT * FROM operationlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(operationLog.starttime))
            {
                sqlwhere += "DATE(operationTime)>=DATE('" + operationLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(operationLog.endtime))
            {
                sqlwhere += "DATE(operationTime)<=DATE('" + operationLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (operationLog.Recordhead > -1 && operationLog.Recordtail > 0)
            {
                strSql += " LIMIT " + operationLog.Recordhead + "," + operationLog.Recordtail;
            }
            return SqlHelper<OperationLog>.SqlList(strSql);
        }

        public int QueryOperationlogCount(OperationLog operationLog)
        {
            string strSql = "SELECT COUNT(1) FROM operationlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(operationLog.starttime))
            {
                sqlwhere += "DATE(operationTime)>=DATE('" + operationLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(operationLog.endtime))
            {
                sqlwhere += "DATE(operationTime)<=DATE('" + operationLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<OperationLog>.SqlScalar(strSql);
        }

        public bool AddClearLog(ClearLog clearLog)
        {
            string strSql = string.Format(@"INSERT INTO clearlog(clearType,clearContent,updateTime)VALUES('{0}','{1}','{2}')",
                clearLog.clearType, clearLog.clearContent, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<ClearLog>.SqlExecute(strSql);
        }

        public List<ClearLog> QueryClearLog(ClearLog clearLog)
        {
            string strSql = "SELECT * FROM clearlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(clearLog.starttime))
            {
                sqlwhere += "DATE(updateTime)>=DATE('" + clearLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(clearLog.endtime))
            {
                sqlwhere += "DATE(updateTime)<=DATE('" + clearLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (clearLog.Recordhead > -1 && clearLog.Recordtail > 0)
            {
                strSql += " LIMIT " + clearLog.Recordhead + "," + clearLog.Recordtail;
            }
            return SqlHelper<ClearLog>.SqlList(strSql);
        }

        public int QueryClearLogCount(ClearLog clearLog)
        {
            string strSql = "SELECT COUNT(1) FROM clearlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(clearLog.starttime))
            {
                sqlwhere += "DATE(updateTime)>=DATE('" + clearLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(clearLog.endtime))
            {
                sqlwhere += "DATE(updateTime)<=DATE('" + clearLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<ClearLog>.SqlScalar(strSql);
        }

        public bool AddLoginLog(LoginLog loginLog)
        {
            string strSql = string.Format(@"INSERT INTO loginlog(loginame,fullname,loginTime,type,remark)VALUES('{0}','{1}','{2}','{3}','{4}')",
                loginLog.loginame, loginLog.fullname, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), loginLog.type,loginLog.remark);

            return SqlHelper<LoginLog>.SqlExecute(strSql);
        }

        public List<LoginLog> QueryLoginLog(LoginLog loginLog)
        {
            string strSql = "SELECT * FROM loginlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(loginLog.starttime))
            {
                sqlwhere += "DATE(loginTime)>=DATE('" + loginLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(loginLog.endtime))
            {
                sqlwhere += "DATE(loginTime)<=DATE('" + loginLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (loginLog.Recordhead > -1 && loginLog.Recordtail > 0)
            {
                strSql += " LIMIT " + loginLog.Recordhead + "," + loginLog.Recordtail;
            }
            return SqlHelper<LoginLog>.SqlList(strSql);
        }

        public int QueryLoginLogCount(LoginLog loginLog)
        {
            string strSql = "SELECT COUNT(1) FROM loginlog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(loginLog.starttime))
            {
                sqlwhere += "DATE(loginTime)>=DATE('" + loginLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(loginLog.endtime))
            {
                sqlwhere += "DATE(loginTime)<=DATE('" + loginLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<LoginLog>.SqlScalar(strSql);
        }

        public bool AddLockLog(LockLog lockLog)
        {
            string strSql = string.Format(@"INSERT INTO loginlog(operation,operatorName,lockTime,remark)VALUES('{0}','{1}','{2}','{3}')",
                lockLog.operation, lockLog.operatorName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), lockLog.remark);

            return SqlHelper<LoginLog>.SqlExecute(strSql);
        }

        public List<LockLog> QueryLockLog(LockLog lockLog)
        {
            string strSql = "SELECT * FROM locklog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(lockLog.starttime))
            {
                sqlwhere += "DATE(lockTime)>=DATE('" + lockLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(lockLog.endtime))
            {
                sqlwhere += "DATE(lockTime)<=DATE('" + lockLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (lockLog.Recordhead > -1 && lockLog.Recordtail > 0)
            {
                strSql += " LIMIT " + lockLog.Recordhead + "," + lockLog.Recordtail;
            }
            return SqlHelper<LockLog>.SqlList(strSql);
        }

        public int QueryLockLogCount(LockLog lockLog)
        {
            string strSql = "SELECT COUNT(1) FROM locklog";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(lockLog.starttime))
            {
                sqlwhere += "DATE(lockTime)>=DATE('" + lockLog.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(lockLog.endtime))
            {
                sqlwhere += "DATE(lockTime)<=DATE('" + lockLog.endtime + "') AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<LockLog>.SqlScalar(strSql);
        }
    }
}
